Schema Introspection# PRO

This section explains how to inspect and analyze database structures using SQLite’s schema introspection APIs.

Schema Introspection allows scripts to query database structure information at runtime, including tables, columns, primary keys, foreign keys, and indexes. These capabilities are commonly used for:

• Data migrations and version management
• Tooling scripts (such as database browsers or analyzers)
• Runtime schema validation
• Debugging and diagnostics

#Getting the Schema Version

#schemaVersion

db.schemaVersion(): Promise<number>

Returns the current database schema version.

This value is typically used to:

• Determine whether a database migration is required
• Integrate with external schema versioning logic

Example:

const version = await db.schemaVersion()

#Checking Table Existence

#tableExists

db.tableExists(tableName: string, schemaName?: string): Promise<boolean>

Checks whether a specified table exists.

• schemaName defaults to the main schema
• Returns true if the table exists

Example:

const exists = await db.tableExists("user")

#Inspecting Columns

#columnsIn

db.columnsIn(tableName: string, schemaName?: string): Promise<ColumnInfo[]>

Returns structural information for all columns in the specified table.

#ColumnInfo

type ColumnInfo = {
  name: string
  type: string
  defaultValueSQL: string | null
  isNotNull: boolean
  primaryKeyIndex: number
}

Field descriptions:

• name: column name
• type: column type
• defaultValueSQL: SQL expression for the default value
• isNotNull: whether the column is NOT NULL
• primaryKeyIndex: order of the column within the primary key (0 if not part of the primary key)

Example:

const columns = await db.columnsIn("user")

#Inspecting Primary Keys

#primaryKey

db.primaryKey(tableName: string, schemaName?: string): Promise<PrimaryKeyInfo>

Returns primary key information for the specified table.

#PrimaryKeyInfo

type PrimaryKeyInfo = {
  columns: string[]
  rowIDColumn: string | null
  isRowID: boolean
}

Field descriptions:

• columns: names of the primary key columns
• rowIDColumn: name of the associated rowid column, if applicable
• isRowID: whether SQLite’s implicit rowid is used as the primary key

Example:

const pk = await db.primaryKey("user")

#Inspecting Foreign Keys

#foreignKeys

db.foreignKeys(tableName: string, schemaName?: string): Promise<ForeignKeyInfo[]>

Returns all foreign key definitions for the specified table.

#ForeignKeyInfo

type ForeignKeyInfo = {
  id: number
  originColumns: string[]
  destinationTable: string
  destinationColumns: string[]
  mapping: {
    origin: string
    destination: string
  }[]
}

Field descriptions:

• id: foreign key identifier
• originColumns: columns in the current table
• destinationTable: referenced table
• destinationColumns: referenced columns
• mapping: one-to-one column mapping

Example:

const fks = await db.foreignKeys("order")

#Inspecting Indexes

#indexes

db.indexes(tableName: string, schemaName?: string): Promise<IndexInfo[]>

Returns all indexes defined on the specified table.

#IndexInfo

type IndexInfo = {
  name: string
  columns: string[]
  isUnique: boolean
  origin: "createIndex" | "primaryKeyConstraint" | "uniqueConstraint"
}

Field descriptions:

• name: index name

• columns: columns included in the index

• isUnique: whether the index is unique

• origin: source of the index

  • "createIndex": created explicitly via createIndex
  • "primaryKeyConstraint": generated by a primary key constraint
  • "uniqueConstraint": generated by a unique constraint

Example:

const indexes = await db.indexes("user")

#Checking Unique Key Combinations

#isTableHasUniqueKeys

db.isTableHasUniqueKeys(
  tableName: string,
  uniqueKeys: string[]
): Promise<boolean>

Checks whether the specified table has a unique constraint or unique index that exactly matches the given column combination.

Example:

const hasUnique = await db.isTableHasUniqueKeys(
  "user",
  ["email"]
)

This method is commonly used to:

• Determine whether a unique index needs to be created
• Avoid redefining constraints during schema initialization or migration

#Usage Recommendations

• Inspect existing schema state before applying structural changes
• Prefer introspection over assumptions in migration logic
• Combine introspection APIs with tooling scripts for analysis or visualization
• Avoid calling schema inspection APIs in high-frequency execution paths

#Summary

Schema Introspection provides runtime visibility into database structure, enabling scripts to safely and reliably understand the current database state.

It is commonly used in combination with:

• Schema Management for defining and modifying structures
• Transactions to ensure atomic schema changes
• Executing SQL & Queries for data operations based on known schemas